﻿using Reflection.Library.Framework;
using Reflection.Library.Framework.AttributeExtend;
using Reflection.Library.Framework.Model;
using Reflection.Library.IDAL;
using Reflection.Library.Model;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;

namespace Reflection.Library.DAL
{
    public class BaseDAL : IBaseDAL
    {
        /// <summary>
        /// 查询单个记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id"></param>
        /// <returns></returns>
        public T Find<T>(int id) where T : BaseModel
        {
            Type type = typeof(T);
            string columnString = string.Join(",", type.GetProperties().Select(p => $"[{p.GetColumnName()}]"));
            string sql = $"select {columnString} from [{type.Name}] where Id={id}";
            T t = (T)Activator.CreateInstance(type);
            using (SqlConnection connection = new SqlConnection(StaticConstant.SqlServerConnectionString))
            {
                SqlCommand command = new SqlCommand(sql, connection);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                List<T> list = this.ReadList<T>(reader);
                t = list.FirstOrDefault();
            }
            return t;
        }

        /// <summary>
        /// 查询所有记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public List<T> FindAll<T>() where T : BaseModel
        {
            Type type = typeof(T);
            string columnString = string.Join(",", type.GetProperties().Select(p => $"[{p.GetColumnName()}]"));
            string sql = $"select {columnString} from [{type.Name}] ";
            List<T> list = new List<T>();
            using (SqlConnection connection = new SqlConnection(StaticConstant.SqlServerConnectionString))
            {
                SqlCommand command = new SqlCommand(sql, connection);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                list = ReadList<T>(reader);
            }
            return list;
        }

        /// <summary>
        /// 更新记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        public void Update<T>(T t) where T : BaseModel
        {
            Type type = typeof(T);
            if (!t.Validate<T>())
            {
                throw new Exception("数据不正确");
            }
            var propArray = type.GetProperties().Where(p => !p.Name.Equals("Id"));
            string columnString = string.Join(",", propArray.Select(p => $"[{p.GetColumnName()}]=@{p.GetColumnName()}"));
            var parameters = propArray.Select(p => new SqlParameter($"@{p.GetColumnName()}", p.GetValue(t) ?? DBNull.Value)).ToArray();
            string sql = $"update {type.Name} set {columnString} where Id={t.Id}";
            using (SqlConnection connection = new SqlConnection(StaticConstant.SqlServerConnectionString))
            {
                SqlCommand command = new SqlCommand(sql, connection);
                command.Parameters.AddRange(parameters);
                connection.Open();
                int result = command.ExecuteNonQuery();
                if (result == 0)
                {
                    throw new Exception("Update 数据不存在");
                }

            }
        }

        /// <summary>
        /// 插入一条记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        public void Insert<T>(T t) where T : BaseModel
        {
            Type type = typeof(T);
            var propArray = type.GetProperties().Where(p => !p.Name.Equals("Id"));
            string insertString = string.Join(",", propArray.Select(p => p.GetColumnName()));
            string columnString = string.Join(",", propArray.Select(p => $"@{p.GetColumnName()}"));
            var parameters = propArray.Select(p => new SqlParameter($"@{p.GetColumnName()}", p.GetValue(t) ?? DBNull.Value)).ToArray();
            string sql = $"Insert into {type.Name} ({insertString}) values({columnString}) ";
            using (SqlConnection connection = new SqlConnection(StaticConstant.SqlServerConnectionString))
            {
                SqlCommand command = new SqlCommand(sql, connection);
                command.Parameters.AddRange(parameters);
                connection.Open();
                int result = command.ExecuteNonQuery();
                if (result == 0)
                {
                    throw new Exception("insert 插入数据失败！");
                }
            }
        }

        /// <summary>
        /// 删除记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        public void Delete<T>(int Id) where T : BaseModel
        {
            Type type = typeof(T);
            string sql = $"delete from {type.Name} where Id={Id}";

            using (SqlConnection connection = new SqlConnection(StaticConstant.SqlServerConnectionString))
            {
                SqlCommand command = new SqlCommand(sql, connection);
                connection.Open();
                int result = command.ExecuteNonQuery();
                if (result == 0)
                {
                    throw new Exception("delete 数据不存在！");
                }
            }

        }

        /// <summary>
        /// 读取数据库并赋值
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="reader"></param>
        /// <returns></returns>
        private List<T> ReadList<T>(SqlDataReader reader) where T : BaseModel
        {
            List<T> list = new List<T>();
            Type type = typeof(T);
            while (reader.Read())
            {
                T t = (T)Activator.CreateInstance(type);
                foreach (var prop in type.GetProperties())
                {
                    object oValue = reader[prop.GetColumnName()];
                    if (oValue is DBNull)
                    {
                        oValue = null;
                    }
                    prop.SetValue(t, oValue);
                }
                list.Add(t);
            }
            return list;
        }

    }
}
